package com.proactive.webapp.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.proactive.webapp.dao.MemberDAO;
import com.proactive.webapp.entity.Member;

public class MemberDAOImpl implements MemberDAO{
	private DataSource dataSource;
	private Connection conn = null ;
	private ResultSet rs = null ;
	private PreparedStatement smt = null ;
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
 
	public void insert(Member member){
		// TODO Auto-generated method stub
		String sql = "INSERT INTO member(memberName,memberAccount,memberPassword,"
				+ "registerNO,memberEmail,memberPhotoType,memberPhoto,registerType,memberNO,registerDate)"
				+ " SELECT ?,?,?,?,?,0,'resources/image/default/userdefault.jpg',0,MAX(memberNO)+1,NOW() FROM member";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1,member.getName());
			smt.setString(2,member.getAccount());
			smt.setString(3,member.getPassword());
			smt.setString(4,member.getRegisterNO());
			smt.setString(5,member.getEmail());
			smt.executeUpdate();			
			smt.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	public void update(Member member) {
		// TODO Auto-generated method stub
		String sql = "UPDATE member SET memberName=?, memberJob=?, memberContent=?, memberPhoto=?, "
				+ "memberPhotoType=?, memberFacebook=?, memberGoogle=?, memberPinterest=? WHERE memberAccount=?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1,member.getName());
			smt.setString(2,member.getJob());
			smt.setString(3,member.getContent());
			smt.setString(4,member.getPhotoPath());
			smt.setInt(5,member.getPhotoType());
			smt.setString(6,member.getFacebook());
			smt.setString(7,member.getGoogle());
			smt.setString(8,member.getPinterest());
			smt.setString(9,member.getAccount());
			smt.executeUpdate();			
			smt.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}
	
	public Member get(Member member) {
		// TODO Auto-generated method stub
		String sql = "SELECT *,(SELECT COUNT(*) FROM follow b WHERE a.memberAccount = b.followMemberAccount) AS fansCount, "
				+ "(SELECT COUNT(*) FROM follow c WHERE a.memberAccount = c.memberAccount) AS followCount "
				+ "FROM member a WHERE memberAccount = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, member.getAccount());
			rs = smt.executeQuery();
			if(rs.next()){
				member.setName(rs.getString("memberName"));
				member.setJob(rs.getString("memberJob"));
				member.setContent(rs.getString("memberContent"));
				member.setFacebook(rs.getString("memberFacebook"));
				member.setGoogle(rs.getString("memberGoogle"));
				member.setPinterest(rs.getString("memberPinterest"));
				member.setMemberNO(rs.getString("memberNO"));
				member.setRegisterType(rs.getInt("registerType"));
				member.setRegisterNO(rs.getString("registerNO"));
				member.setResetNO(rs.getString("resetNO"));
				member.setPhotoType(rs.getInt("memberPhotoType"));
				member.setPhotoName(rs.getString("memberPhoto"));
				member.setPhotoPath((member.getPhotoType()==2?"":"https://s3-ap-northeast-1.amazonaws.com/proactive/")+member.getPhotoName());
				member.setFansCount(rs.getInt("fansCount"));
				member.setFollowCount(rs.getInt("followCount"));
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return member;
	}

	public Member checkLogin(Member member) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM member WHERE memberAccount=? AND memberPassword=? "
				+ "AND memberStatus=1";
		member.setLogin(false);
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, member.getAccount());
			smt.setString(2, member.getPassword());
			rs = smt.executeQuery();
			if(rs.next()){
				member.setPassword("");
				member.setName(rs.getString("memberName"));
				member.setJob(rs.getString("memberJob"));
				member.setContent(rs.getString("memberContent"));
				member.setFacebook(rs.getString("memberFacebook"));
				member.setGoogle(rs.getString("memberGoogle"));
				member.setPinterest(rs.getString("memberPinterest"));
				member.setMemberNO(rs.getString("memberNO"));
				member.setPhotoType(rs.getInt("memberPhotoType"));
				member.setPhotoName(rs.getString("memberPhoto"));
				member.setPhotoPath((member.getPhotoType()==2?"":"https://s3-ap-northeast-1.amazonaws.com/proactive/")+member.getPhotoName());
				member.setRegisterType(rs.getInt("registerType"));
				member.setLogin(true);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return member;
	}

	@Override
	public Member getByLogin(Member member, String loginAccount) {
		// TODO Auto-generated method stub
		String sql = "SELECT *,(SELECT COUNT(*) FROM follow b WHERE a.memberAccount = b.followMemberAccount) AS fansCount, "
				+ "(SELECT COUNT(*) FROM follow c WHERE a.memberAccount = c.memberAccount) AS followCount, "
				+ "(SELECT COUNT(*) FROM follow d WHERE a.memberAccount = d.followMemberAccount AND d.memberAccount = ? "
				+ "AND d.followMemberAccount = ?) AS follow FROM member a WHERE memberAccount = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, loginAccount);
			smt.setString(2, member.getAccount());
			smt.setString(3, member.getAccount());
			rs = smt.executeQuery();
			if(rs.next()){
				member.setName(rs.getString("memberName"));
				member.setJob(rs.getString("memberJob"));
				member.setContent(rs.getString("memberContent"));
				member.setFacebook(rs.getString("memberFacebook"));
				member.setGoogle(rs.getString("memberGoogle"));
				member.setPinterest(rs.getString("memberPinterest"));
				member.setMemberNO(rs.getString("memberNO"));
				member.setRegisterNO(rs.getString("registerNO"));
				member.setResetNO(rs.getString("resetNO"));
				member.setPhotoType(rs.getInt("memberPhotoType"));
				member.setPhotoName(rs.getString("memberPhoto"));
				member.setPhotoPath((member.getPhotoType()==2?"":"https://s3-ap-northeast-1.amazonaws.com/proactive/")+member.getPhotoName());
				member.setFansCount(rs.getInt("fansCount"));
				member.setFollowCount(rs.getInt("followCount"));
				member.setFollowFlag(rs.getInt("follow")==0?false:true);
				member.setFollowClass(member.isFollowFlag()?"followHover":"");
				member.setMemberFollowClass(member.isFollowFlag()?"cancelattention":"footernav");
				member.setFollowString(member.isFollowFlag()?"已關注":"+關注");
				member.setMyselfFlag(member.getAccount().equals(loginAccount)?true:false);
				member.setRegisterType(rs.getInt("registerType"));
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return member;
	}

	
	public Member getByMemberNO(Member member) {
		// TODO Auto-generated method stub
		String sql = "SELECT *,(SELECT COUNT(*) FROM follow b WHERE a.memberAccount = b.followMemberAccount) AS fansCount, "
				+ "(SELECT COUNT(*) FROM follow c WHERE a.memberAccount = c.memberAccount) AS followCount "
				+ "FROM member a WHERE memberNO = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, member.getMemberNO());
			rs = smt.executeQuery();
			if(rs.next()){
				member.setAccount(rs.getString("memberAccount"));
				member.setName(rs.getString("memberName"));
				member.setJob(rs.getString("memberJob"));
				member.setContent(rs.getString("memberContent"));
				member.setFacebook(rs.getString("memberFacebook"));
				member.setGoogle(rs.getString("memberGoogle"));
				member.setPinterest(rs.getString("memberPinterest"));
				member.setMemberNO(rs.getString("memberNO"));
				member.setRegisterNO(rs.getString("registerNO"));
				member.setResetNO(rs.getString("resetNO"));
				member.setPhotoType(rs.getInt("memberPhotoType"));
				member.setPhotoName(rs.getString("memberPhoto"));
				member.setPhotoPath((member.getPhotoType()==2?"":"https://s3-ap-northeast-1.amazonaws.com/proactive/")+member.getPhotoName());
				member.setFansCount(rs.getInt("fansCount"));
				member.setFollowCount(rs.getInt("followCount"));
			}
			smt.executeQuery();	
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return member;
	}

	@Override
	public boolean checkAccount(String account) {
		// TODO Auto-generated method stub
		boolean flag = true;
		String sql = "SELECT * FROM member WHERE memberAccount = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			rs = smt.executeQuery();
			if(rs.next()){
				flag = false;
			}
			smt.executeQuery();	
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return flag;
	}

	@Override
	public Member getByRegisterNO(Member member) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM member WHERE registerNO=?";
		member.setLogin(false);
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, member.getRegisterNO());
			rs = smt.executeQuery();
			if(rs.next()){
				member.setAccount(rs.getString("memberAccount"));
				member.setName(rs.getString("memberName"));
				member.setJob(rs.getString("memberJob"));
				member.setContent(rs.getString("memberContent"));
				member.setFacebook(rs.getString("memberFacebook"));
				member.setGoogle(rs.getString("memberGoogle"));
				member.setPinterest(rs.getString("memberPinterest"));
				member.setMemberNO(rs.getString("memberNO"));
				member.setPhotoType(rs.getInt("memberPhotoType"));
				member.setPhotoName(rs.getString("memberPhoto"));
				member.setStatus(rs.getInt("memberStatus"));
				member.setPhotoPath((member.getPhotoType()==2?"":"https://s3-ap-northeast-1.amazonaws.com/proactive/")+member.getPhotoName());
				member.setLogin(true);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return member;
	}

	@Override
	public void updateStatus(Member member) {
		// TODO Auto-generated method stub
		String sql = "UPDATE member SET memberStatus=1, registerNO='', resendDate=null "
				+ "WHERE memberAccount=?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1,member.getAccount());
			smt.executeUpdate();	
			smt.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	@Override
	public List<Member> getFollowList(String account, String loginAccount, int start, int size) {
		// TODO Auto-generated method stub
		List<Member> memberList = new ArrayList<Member>();
		String sql = "SELECT *,(SELECT COUNT(*) FROM course b WHERE a.followMemberAccount=b.memberAccount AND courseStatus=1)"
				+ "AS courseCount,(SELECT COUNT(*) FROM design c WHERE a.followMemberAccount=c.memberAccount AND designStatus=1)"
				+ "AS designCount,(SELECT COUNT(*) FROM follow d WHERE a.followMemberAccount = d.followMemberAccount)"
				+ "AS fansCount,(SELECT COUNT(*) FROM follow e WHERE a.followMemberAccount = e.followMemberAccount AND e.memberAccount=?)"
				+ "AS follow FROM follow a JOIN member f ON a.followMemberAccount = f.memberAccount "
				+ "WHERE a.memberAccount = ? AND f.memberStatus = 1 LIMIT ?,?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, loginAccount);
			smt.setString(2, account);
			smt.setInt(3, start);
			smt.setInt(4, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Member member = new Member();
				member.setAccount(rs.getString("f.memberAccount"));
				member.setName(rs.getString("memberName"));
				member.setJob(rs.getString("memberJob"));
				member.setContent(rs.getString("memberContent"));
				member.setFacebook(rs.getString("memberFacebook"));
				member.setGoogle(rs.getString("memberGoogle"));
				member.setPinterest(rs.getString("memberPinterest"));
				member.setMemberNO(rs.getString("memberNO"));
				member.setRegisterNO(rs.getString("registerNO"));
				member.setResetNO(rs.getString("resetNO"));
				member.setPhotoType(rs.getInt("memberPhotoType"));
				member.setPhotoName(rs.getString("memberPhoto"));
				member.setPhotoPath((member.getPhotoType()==2?"":"https://s3-ap-northeast-1.amazonaws.com/proactive/")+member.getPhotoName());
				member.setFansCount(rs.getInt("fansCount"));
				member.setCourseCount(rs.getInt("courseCount"));
				member.setDesignCount(rs.getInt("designCount"));
				member.setFollowFlag(rs.getInt("follow")==0?false:true);
				memberList.add(member);
			}
			smt.executeQuery();	
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return memberList;
	}

	@Override
	public List<Member> getFansList(String account, String loginAccount, int start, int size) {
		// TODO Auto-generated method stub
		List<Member> memberList = new ArrayList<Member>();
		String sql = "SELECT *,(SELECT COUNT(*) FROM course b WHERE a.memberAccount=b.memberAccount AND courseStatus=1)"
				+ "AS courseCount,(SELECT COUNT(*) FROM design c WHERE a.memberAccount=c.memberAccount AND designStatus=1)"
				+ "AS designCount,(SELECT COUNT(*) FROM follow d WHERE a.memberAccount = d.followMemberAccount)"
				+ "AS fansCount,(SELECT COUNT(*) FROM follow e WHERE a.memberAccount = e.followMemberAccount AND e.memberAccount=?)"
				+ "AS follow FROM follow a JOIN member f ON a.memberAccount = f.memberAccount "
				+ "WHERE followMemberAccount = ? AND f.memberStatus = 1 LIMIT ?,?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, loginAccount);
			smt.setString(2, account);
			smt.setInt(3, start);
			smt.setInt(4, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Member member = new Member();
				member.setAccount(rs.getString("f.memberAccount"));
				member.setName(rs.getString("memberName"));
				member.setJob(rs.getString("memberJob"));
				member.setContent(rs.getString("memberContent"));
				member.setFacebook(rs.getString("memberFacebook"));
				member.setGoogle(rs.getString("memberGoogle"));
				member.setPinterest(rs.getString("memberPinterest"));
				member.setMemberNO(rs.getString("memberNO"));
				member.setRegisterNO(rs.getString("registerNO"));
				member.setResetNO(rs.getString("resetNO"));
				member.setPhotoType(rs.getInt("memberPhotoType"));
				member.setPhotoName(rs.getString("memberPhoto"));
				member.setPhotoPath((member.getPhotoType()==2?"":"https://s3-ap-northeast-1.amazonaws.com/proactive/")+member.getPhotoName());
				member.setFansCount(rs.getInt("fansCount"));
				member.setCourseCount(rs.getInt("courseCount"));
				member.setDesignCount(rs.getInt("designCount"));
				member.setFollowFlag(rs.getInt("follow")==0?false:true);
				memberList.add(member);
			}
			smt.executeQuery();	
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return memberList;
	}

	@Override
	public int countFansTotal(String account) {
		// TODO Auto-generated method stub
		int count = 0;
		String sql = "SELECT COUNT(*) AS countTotal FROM follow a "
				+ "JOIN member b ON a.memberAccount = b.memberAccount "
				+ "WHERE a.followMemberAccount = ? AND b.memberStatus = 1";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			rs = smt.executeQuery();
			if(rs.next()){
				count = rs.getInt("countTotal");
			}
			smt.executeQuery();	
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	@Override
	public int countFollowTotal(String account) {
		// TODO Auto-generated method stub
		int count = 0;
		String sql = "SELECT COUNT(*) AS countTotal FROM follow a "
				+ "JOIN member b ON a.followMemberAccount = b.memberAccount "
				+ "WHERE a.memberAccount = ? AND b.memberStatus = 1";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			rs = smt.executeQuery();
			if(rs.next()){
				count = rs.getInt("countTotal");
			}
			smt.executeQuery();	
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	@Override
	public List<Member> getListTop(int number) {
		// TODO Auto-generated method stub
		List<Member> memberList = new ArrayList<Member>();
		String sql = "SELECT memberAccount,memberNO,memberName,memberJob,memberPhotoType,memberPhoto,(courseCount+designCount+worksheetCount) AS totalCount "
				+ "FROM(SELECT *,(SELECT COUNT(*) FROM course b WHERE a.memberAccount=b.memberAccount AND courseStatus=1) "
				+ "AS courseCount,(SELECT COUNT(*) FROM design c WHERE a.memberAccount=c.memberAccount AND designStatus=1) "
				+ "AS designCount,(SELECT COUNT(*) FROM worksheet d WHERE a.memberAccount=d.memberAccount AND worksheetStatus=1) "
				+ "AS worksheetCount FROM member a) newTable HAVING totalCount>0 ORDER BY totalCount DESC LIMIT ?";
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, number);
			rs = smt.executeQuery();
			while(rs.next()){
				Member member = new Member();
				member.setAccount(rs.getString("memberAccount"));
				member.setName(rs.getString("memberName"));
				member.setJob(rs.getString("memberJob"));
				member.setMemberNO(rs.getString("memberNO"));
				member.setPhotoType(rs.getInt("memberPhotoType"));
				member.setPhotoName(rs.getString("memberPhoto"));
				member.setPhotoPath((member.getPhotoType()==2?"":"https://s3-ap-northeast-1.amazonaws.com/proactive/")+member.getPhotoName());
				memberList.add(member);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return memberList;
	}

	@Override
	public Member checkMember(String account) {
		// TODO Auto-generated method stub
		Member member = new Member();
		String sql = "SELECT * FROM member WHERE memberAccount=?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			rs = smt.executeQuery();
			if(rs.next()){
				member.setAccount(account);
				member.setName(rs.getString("memberName"));
				member.setJob(rs.getString("memberJob"));
				member.setContent(rs.getString("memberContent"));
				member.setFacebook(rs.getString("memberFacebook"));
				member.setGoogle(rs.getString("memberGoogle"));
				member.setPinterest(rs.getString("memberPinterest"));
				member.setMemberNO(rs.getString("memberNO"));
				member.setPhotoType(rs.getInt("memberPhotoType"));
				member.setPhotoName(rs.getString("memberPhoto"));
				member.setStatus(rs.getInt("memberStatus"));
				member.setRegisterNO(rs.getString("registerNO"));
				member.setRegisterDate(rs.getDate("registerDate"));
				member.setResetNO(rs.getString("resetNO"));
				member.setResetDate(rs.getDate("resetDate"));
				member.setPhotoPath((member.getPhotoType()==2?"":"https://s3-ap-northeast-1.amazonaws.com/proactive/")+member.getPhotoName());
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return member;
	}

	@Override
	public void updateRegisterNO(Member member) {
		// TODO Auto-generated method stub
		String sql = "UPDATE member SET registerNO = ?, resendDate = NOW() WHERE memberAccount = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1,member.getRegisterNO());
			smt.setString(2,member.getAccount());
			smt.executeUpdate();			
			smt.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	@Override
	public void updateResetNO(Member member) {
		// TODO Auto-generated method stub
		String sql = "UPDATE member SET resetNO = ?, resetDate = NOW() WHERE memberAccount = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1,member.getResetNO());
			smt.setString(2,member.getAccount());
			smt.executeUpdate();			
			smt.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	@Override
	public Member getByResetNO(Member member) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM member WHERE resetNO=?";
		member.setLogin(false);
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, member.getResetNO());
			rs = smt.executeQuery();
			if(rs.next()){
				member.setAccount(rs.getString("memberAccount"));
				member.setName(rs.getString("memberName"));
				member.setJob(rs.getString("memberJob"));
				member.setContent(rs.getString("memberContent"));
				member.setFacebook(rs.getString("memberFacebook"));
				member.setGoogle(rs.getString("memberGoogle"));
				member.setPinterest(rs.getString("memberPinterest"));
				member.setMemberNO(rs.getString("memberNO"));
				member.setPhotoType(rs.getInt("memberPhotoType"));
				member.setPhotoName(rs.getString("memberPhoto"));
				member.setStatus(rs.getInt("memberStatus"));
				member.setPhotoPath((member.getPhotoType()==2?"":"https://s3-ap-northeast-1.amazonaws.com/proactive/")+member.getPhotoName());
				member.setLogin(true);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return member;
	}

	@Override
	public void updatePassword(Member member) {
		// TODO Auto-generated method stub
		String sql = "UPDATE member SET memberPassword=?, resetNO='', resetDate=NULL "
				+ "WHERE memberAccount=?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1,member.getPassword());
			smt.setString(2,member.getAccount());
			smt.executeUpdate();			
			smt.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	@Override
	public List<Member> getListTopByCard(int start, int size) {
		// TODO Auto-generated method stub
		List<Member> memberList = new ArrayList<Member>();
		String sql = "SELECT *,(SELECT count(*) FROM card b WHERE a.memberAccount = b.memberAccount) "
				+ "AS cardCount FROM member a ORDER BY cardCount DESC LIMIT ?,?";
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, start);
			smt.setInt(2, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Member member = new Member();
				member.setAccount(rs.getString("memberAccount"));
				member.setName(rs.getString("memberName"));
				member.setJob(rs.getString("memberJob"));
				member.setMemberNO(rs.getString("memberNO"));
				member.setPhotoType(rs.getInt("memberPhotoType"));
				member.setPhotoName(rs.getString("memberPhoto"));
				member.setPhotoPath((member.getPhotoType()==2?"":"https://s3-ap-northeast-1.amazonaws.com/proactive/")+member.getPhotoName());
				member.setCardCount(rs.getInt("cardCount"));
				memberList.add(member);
			}
			smt.executeQuery();	
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return memberList;
	}

	@Override
	public Member checkFacebookAccount(Member member) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM member WHERE memberAccount = ? AND registerType = 1";
		member.setLogin(false);
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, member.getAccount());
			rs = smt.executeQuery();
			if(rs.next()){
				member.setAccount(rs.getString("memberAccount"));
				member.setName(rs.getString("memberName"));
				member.setJob(rs.getString("memberJob"));
				member.setContent(rs.getString("memberContent"));
				member.setFacebook(rs.getString("memberFacebook"));
				member.setGoogle(rs.getString("memberGoogle"));
				member.setPinterest(rs.getString("memberPinterest"));
				member.setMemberNO(rs.getString("memberNO"));
				member.setPhotoType(rs.getInt("memberPhotoType"));
				member.setPhotoName(rs.getString("memberPhoto"));
				member.setStatus(rs.getInt("memberStatus"));
				member.setPhotoPath((member.getPhotoType()==2?"":"https://s3-ap-northeast-1.amazonaws.com/proactive/")+member.getPhotoName());
				member.setRegisterType(rs.getInt("registerType"));
				member.setLogin(true);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return member;
	}

	@Override
	public void insertByFacebook(Member member) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO member(memberName,memberAccount,memberEmail,memberFacebook,"
				+ "memberPhoto,memberPhotoType,registerType,memberNO,registerDate) "
				+ "SELECT ?,?,?,?,?,2,1,MAX(memberNO)+1,NOW() FROM member";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1,member.getName());
			smt.setString(2,member.getAccount());
			smt.setString(3,member.getEmail());
			smt.setString(4,member.getFacebook());
			smt.setString(5,member.getPhotoPath());
			smt.executeUpdate();			
			smt.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}


}
